﻿-- =============================================
-- Author:		Sandy Stewart
-- Create date: 
-- Description:	Returns the first title sorted alphabetically 
-- for a particular parliamentarian (by cpal id) except for the 
-- special case of treasurer
-- =============================================
CREATE FUNCTION [dbo].[fnFirstTitle] 
(
	-- Add the parameters for the function here
	@CPALId int
)
RETURNS varchar(100)
AS
BEGIN
	DECLARE @FirstTitle varchar(100);
	Declare @AllTitlesTemp TABLE (FullTitle varchar(1000));

	Insert into @AllTitlesTemp (FullTitle)
	Select FullTitle
     FROM
			dbo.Positions RIGHT OUTER JOIN 
				dbo.ParliamentarianPositions on Positions.Id = ParliamentarianPositions.Positions_Id
				FULL OUTER JOIN
				dbo.Parliamentarians on ParliamentarianPositions.Parliamentarian_CPALId = Parliamentarians.CPALId
     WHERE dbo.Parliamentarians.CPALId = @CPALId
     ORDER BY dbo.Positions.FullTitle ASC;
	
	-- special case for treasurer - must appear first in sort order
	If (Select Count(*) from @AllTitlesTemp where FullTitle = 'Treasurer') > 0
	Begin
		Select @FirstTitle = 'Treasurer';
	end
	Else
	Begin
		Select TOP(1) @FirstTitle = FullTitle
					From @AllTitlesTemp;
	End
	
	Return @FirstTitle

END